package com.cs410.collabwriting.tools;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedList;

import com.cs410.collabwriting.tools.queryTools.DatabaseTable;
import com.cs410.collabwriting.tools.queryTools.QueryBuilder;

public class DatabaseManager {

	private static LinkedList<Connection> e_connectionPool = null;
	private static volatile DatabaseManager e_dbManager = null;
	
	// Default Values of Database Parameters
	private static int e_numberOfConnections = 4;
	private static String e_username = "mysql";
	private static String e_password = "mysql";
	private static String e_dbDriver = "com.mysql.jdbc.Driver";
	private static String e_url = "jdbc:mysql://localhost";
	
	private DatabaseManager() {
		LogManager.logInfo("[DBManager] -- Initializing Database Manager & Connection Pool" );
		e_connectionPool = new LinkedList<Connection>();
		
		for( int i = 0; i < e_numberOfConnections; i++ ) {
			try {
				Connection conn = DriverManager.getConnection( e_url, e_username, e_password );
				conn.setAutoCommit( false );
				e_connectionPool.add( conn );
				LogManager.logDebug("[DBManager] -- Connection #" + i + " established to the database", LogManager.LOG_LEVEL_2 );
			} catch ( SQLException sqlException ) {
				LogManager.logError("[DBManager] -- Could not create connections to Database! Reason: " + sqlException.getMessage() );
				LogManager.logError("[DBManager] -- Failed to initialize the connection pool. Quitting.");
			}
		}
		
		if( e_connectionPool.size() == e_numberOfConnections ) {
			LogManager.logInfo("[DBManager] -- All " + e_numberOfConnections + " Connections Established" );
		} else {
			LogManager.logError("[DBMAnager] -- Not All Connections were Established. " + 
					e_connectionPool.size() + "/" + e_numberOfConnections + " are active!");
		}
	}
	
	public static String executeExactQuery( QueryBuilder query ) {
		initializeDatabaseManager();
		Connection connectionFromPool = null;
		
		synchronized( e_connectionPool ) {
			connectionFromPool = e_connectionPool.pollFirst();
		}
		
		if( connectionFromPool != null ) {
			PreparedStatement sqlStatement;
			try {
				String queryString = query.toString();
				sqlStatement = connectionFromPool.prepareStatement( queryString );
				LinkedList<Object> variables = query.getVariables();
				for( int i = 0; i < variables.size(); i++ ) {
					Object item = variables.get( i );
					if( item instanceof Integer ) {
						sqlStatement.setInt( i+1, (Integer) item ) ;
					} else if ( item instanceof String) {
						sqlStatement.setString( i+1 , (String) item );
					}
				}
				ResultSet results = sqlStatement.executeQuery();
				ArrayList<String[]> parsedResults = parseResultSet( results, false );
				results.close();
				sqlStatement.close();
				connectionFromPool.commit();
				LogManager.logDebug("[DBManager] -- Execute Successful : " + queryString, LogManager.LOG_LEVEL_1 );
				return parsedResults.size() > 0 ? parsedResults.get( 0 )[0] : null;
			} catch ( SQLException sqlException ) {
				LogManager.logError("[DBManager] -- Could not execute query! Reason: " + sqlException.getMessage() );
			} finally {
				try {
					if( connectionFromPool.isValid( 5 ) ) {
						LogManager.logDebug("[DBManager] -- Returning connection to pool. Connection ID: " + connectionFromPool.toString(), LogManager.LOG_LEVEL_3 );
						boolean returnConnection = e_connectionPool.add( connectionFromPool );
						LogManager.logDebug("[DBManager] -- Connection Returned : " + returnConnection, LogManager.LOG_LEVEL_3 );
					} else {
						connectionFromPool.close();
					}
				} catch ( SQLException sqlException ) {
					LogManager.logError("[DBManager] -- Error Closing the Connection. Reason: " +  sqlException.getMessage() );
				}
			}
		} else {
			LogManager.logError("[DBManager] -- Error, Conection retrieved from pool was null");
		}
		return null;
	}
	
	// Used for Selection Queries
	public static ArrayList<String[]> executeQuery( QueryBuilder query ) {
		initializeDatabaseManager();
		Connection connectionFromPool = null;
		
		synchronized( e_connectionPool ) {
			connectionFromPool = e_connectionPool.pollFirst();
		}
		
		if( connectionFromPool != null ) {
			PreparedStatement sqlStatement;
			try {
				String queryString = query.toString();
				sqlStatement = connectionFromPool.prepareStatement( queryString );
				LinkedList<Object> variables = query.getVariables();
				for( int i = 0; i < variables.size(); i++ ) {
					Object item = variables.get( i );
					if( item instanceof Integer ) {
						sqlStatement.setInt( i+1, (Integer) item ) ;
					} else if ( item instanceof String) {
						sqlStatement.setString( i+1 , (String) item );
					}
				}
				ResultSet results = sqlStatement.executeQuery();
				ArrayList<String[]> parsedResults = parseResultSet( results, false );
				results.close();
				sqlStatement.close();
				connectionFromPool.commit();
				LogManager.logDebug("[DBManager] -- Execute Successful : " + queryString, LogManager.LOG_LEVEL_1 );
				return parsedResults;
			} catch ( SQLException sqlException ) {
				LogManager.logError("[DBManager] -- Could not execute query! Reason: " + sqlException.getMessage() );
			} finally {
				try {
					if( connectionFromPool.isValid( 5 ) ) {
						LogManager.logDebug("[DBManager] -- Returning connection to pool. Connection ID: " + connectionFromPool.toString(), LogManager.LOG_LEVEL_3 );
						boolean returnConnection = e_connectionPool.add( connectionFromPool );
						LogManager.logDebug("[DBManager] -- Connection Returned : " + returnConnection, LogManager.LOG_LEVEL_3 );
					} else {
						connectionFromPool.close();
					}
				} catch ( SQLException sqlException ) {
					LogManager.logError("[DBManager] -- Error Closing the Connection. Reason: " +  sqlException.getMessage() );
				}
			}
		} else {
			LogManager.logError("[DBManager] -- Error, Conection retrieved from pool was null");
		}
		return new ArrayList<String[]>();
	}
	
	public static ArrayList<String[]> executeQueryWithColumnNames( QueryBuilder query ) {
		initializeDatabaseManager();
		Connection connectionFromPool = null;
		
		synchronized( e_connectionPool ) {
			connectionFromPool = e_connectionPool.pollFirst();
		}
		
		if( connectionFromPool != null ) {
			PreparedStatement sqlStatement;
			try {
				String queryString = query.toString();
				sqlStatement = connectionFromPool.prepareStatement( queryString );
				LinkedList<Object> variables = query.getVariables();
				for( int i = 0; i < variables.size(); i++ ) {
					Object item = variables.get( i );
					if( item instanceof Integer ) {
						sqlStatement.setInt( i+1, (Integer) item ) ;
					} else if ( item instanceof String) {
						sqlStatement.setString( i+1 , (String) item );
					}
				}
				ResultSet results = sqlStatement.executeQuery();
				ArrayList<String[]> parsedResults = parseResultSet( results, true );
				results.close();
				sqlStatement.close();
				connectionFromPool.commit();
				LogManager.logDebug("[DBManager] -- Execute Successful : " + queryString, LogManager.LOG_LEVEL_1 );
				return parsedResults;
			} catch ( SQLException sqlException ) {
				LogManager.logError("[DBManager] -- Could not execute query! Reason: " + sqlException.getMessage() );
			} finally {
				try {
					if( connectionFromPool.isValid( 5 ) ) {
						LogManager.logDebug("[DBManager] -- Returning connection to pool. Connection ID: " + connectionFromPool.toString(), LogManager.LOG_LEVEL_3 );
						boolean returnConnection = e_connectionPool.add( connectionFromPool );
						LogManager.logDebug("[DBManager] -- Connection Returned : " + returnConnection, LogManager.LOG_LEVEL_3 );
					} else {
						connectionFromPool.close();
					}
				} catch ( SQLException sqlException ) {
					LogManager.logError("[DBManager] -- Error Closing the Connection. Reason: " +  sqlException.getMessage() );
				}
			}
		} else {
			LogManager.logError("[DBManager] -- Error, Connection retrieved from pool was null");
		}
		return new ArrayList<String[]>();
	}
	
	// Used for Insert, Update and Delete Queries, returns number of affected rows
	public static int executeUpdate( QueryBuilder query ) {
		initializeDatabaseManager();
		Connection connectionFromPool = null;
		
		synchronized( e_connectionPool ) {
			connectionFromPool = e_connectionPool.pollFirst();
		}
		
		int rowsUpdated = -1;
		if( connectionFromPool != null ) {
			PreparedStatement sqlStatement;
			try {
				String queryString = query.toString();
				sqlStatement = connectionFromPool.prepareStatement( queryString );
				LinkedList<Object> variables = query.getVariables();
				for( int i = 0; i < variables.size(); i++ ) {
					Object item = variables.get( i );
					if( item instanceof Integer ) {
						sqlStatement.setInt( i+1, (Integer) item ) ;
					} else if ( item instanceof String) {
						sqlStatement.setString( i+1 , (String) item );
					}
				}
				rowsUpdated = sqlStatement.executeUpdate();
				sqlStatement.close();
				connectionFromPool.commit();
				LogManager.logDebug("[DBManager] -- Execute Successful : " + queryString, LogManager.LOG_LEVEL_1 );
			} catch (SQLException sqlException ) {
				LogManager.logError("[DBManager] -- Could not execute query! Reason: " + sqlException.getMessage() );
			} finally {
				try {
					if( connectionFromPool.isValid( 5 ) ) {
						LogManager.logDebug("[DBManager] -- Returning connection to pool. Connection ID: " + connectionFromPool.toString(), LogManager.LOG_LEVEL_3 );
						boolean returnConnection = e_connectionPool.add( connectionFromPool );
						LogManager.logDebug("[DBManager] -- Connection Returned : " + returnConnection, LogManager.LOG_LEVEL_3 );
					} else {
						connectionFromPool.close();
					}
				} catch ( SQLException sqlException ) {
					LogManager.logError("[DBManager] -- Error Closing the Connection. Reason: " +  sqlException.getMessage() );
				}
			}
		} else {
			LogManager.logError("[DBManager] -- Error, Connection retrieved from pool was null");
		}
		
		return rowsUpdated;
	} 
		
	// Used for Insert, Update and Delete Queries, returns number of affected rows
		public static int executeUpdate( String query ) {
			initializeDatabaseManager();
			Connection connectionFromPool = null;
			
			synchronized( e_connectionPool ) {
				connectionFromPool = e_connectionPool.pollFirst();
			}
			
			int rowsUpdated = -1;
			if( connectionFromPool != null ) {
				Statement sqlStatement;
				try {
					sqlStatement = connectionFromPool.createStatement();
					rowsUpdated = sqlStatement.executeUpdate( query );
					connectionFromPool.commit();
					sqlStatement.close();
					LogManager.logDebug("[DBManager] -- Execute Successful : " + query, LogManager.LOG_LEVEL_1 );
				} catch (SQLException sqlException ) {
					LogManager.logError("[DBManager] -- Could not execute query! Reason: " + sqlException.getMessage() );
				} finally {
					try {
						if( connectionFromPool.isValid( 5 ) ) {
							LogManager.logDebug("[DBManager] -- Returning connection to pool. Connection ID: " + connectionFromPool.toString(), LogManager.LOG_LEVEL_3 );
							boolean returnConnection = e_connectionPool.add( connectionFromPool );
							LogManager.logDebug("[DBManager] -- Connection Returned : " + returnConnection, LogManager.LOG_LEVEL_3 );
						} else {
							connectionFromPool.close();
						}
					} catch ( SQLException sqlException ) {
						LogManager.logError("[DBManager] -- Error Closing the Connection. Reason: " +  sqlException.getMessage() );
					}
				}
			} else {
				LogManager.logError("[DBManager] -- Error, Connection retrieved from pool was null");
			}
			
			return rowsUpdated;
		} 
		
	public static ArrayList<String> getValuesInColumn( ArrayList<String[]> results, String columnName ) {
		ArrayList<String> valueList = new ArrayList<String>();
		if( results != null && results.size() > 0 ) {
			String[] columnNames = results.get( 0 );
			int index = -1;
			
			for( int i = 0; i < columnNames.length && index < 0; i++ ) {
				if( columnNames[i].equalsIgnoreCase( columnName ) ) {
					index = i;
				}
			}
			if( index >= 0 ) {
				for( int i = 1; i < results.size(); i++ ) {
					String[] columns = results.get( i );
					valueList.add( columns[index] );
				}
			}
		}
		return valueList;
	}
	
	public static String getFirstValueInColumn( ArrayList<String[]> results, String columnName ) {
		if( results != null && results.size() > 0 ) {
			String[] columnNames = results.get( 0 );
			int index = -1;
		
			for( int i = 0; i < columnNames.length && index < 0; i++ ) {
				if( columnNames[i].equalsIgnoreCase( columnName ) ) {
					index = i;
				}
			}
			if( index >= 0 ) {
				return results.get( 1 )[index]; 
			}
		}
		return null;
	}
	
	public static DatabaseManager initializeDatabaseManager() {
		if( e_dbManager == null ) {
			DatabaseManager.InitializeDatabaseConnectionParameters();
		}
		return DatabaseManager.getInstance();
	}
	
	private static void InitializeDatabaseConnectionParameters() {
		ConfigProperties configProp = new ConfigProperties("config.properties");
		e_username = configProp.getValue("DATABASE_USERNAME");
		e_password = configProp.getValue("DATABASE_PASSWORD");
		e_url = configProp.getValue("DATABASE_URL") + "/" + configProp.getValue("DATABASE_NAME");
		e_numberOfConnections = Integer.parseInt( configProp.getValue("DATABASE_CONNECTIONS") );
		e_dbDriver = configProp.getValue("DATABASE_DRIVER");
		configProp.close();
		
		try {
			Class.forName( e_dbDriver ).newInstance();
		} catch ( Exception exception ) {
			LogManager.logError("[DBManager] -- Could not load " + e_dbDriver + " Reason : " + exception.getMessage() );
		}
	}
	
	private static DatabaseManager getInstance() {
		if( e_dbManager == null || e_connectionPool.isEmpty() ) {
			e_dbManager = new DatabaseManager();
			if( e_connectionPool.isEmpty() ) {
				e_dbManager = null;
			}
		}
		return e_dbManager;
	}	

	private static ArrayList<String[]> parseResultSet( ResultSet results , boolean columnHeader) {
		if( results != null )
		try {
			int columnNumber = results.getMetaData().getColumnCount();
			ArrayList<String[]> resultList = new ArrayList<String[]>();
			String[] row = new String[columnNumber];
			
			if( columnHeader ) {
				for( int i = 0; i < columnNumber; i++ ) {
					row[i] = results.getMetaData().getColumnLabel( i + 1 );
				}
				resultList.add( row );
			}
			
			while( results.next() ) {
				row = new String[columnNumber];
				for( int i = 0; i < columnNumber; i++ ) {
					row[i] = results.getString( i + 1 );
				}
				resultList.add( row ) ;
			}
			return resultList;
		} catch ( SQLException sqlException ) {
			LogManager.logError("[DBManager] -- Error Parsing Query Results. Reason: " + sqlException.getMessage() );
		}
		return null;
	}
	
	public static boolean isValidDatabaseTableAndColumn( String input ) {
		boolean validTable = false;
		boolean validColumn = false;
		if( input.contains( "." ) ) {
			String[] tuple = input.split("\\.");
			for( String tableName : DatabaseTable.getAllTableNames() ) {
				if( tuple[0].equalsIgnoreCase( tableName ) ) {
					validTable = true;
				}
			}
			if( tuple.length > 0 && tuple[1].contains(" ") == false && tuple[1].contains("'") == false && tuple[1].contains(";") == false) {
				validColumn = true;
			}
			
		}
		return validTable && validColumn;
	}
}
